import pandas as pd
import numpy as np
import seaborn as sns 
import matplotlib.pyplot as plt

import os 

os.listdir(r"C:\Users\kisha\OneDrive\Desktop\DOCUMENTS\data analyst\projects\Datasets")

['other-American_B01362.csv',
 'other-Carmel_B00256.csv',
 'other-Dial7_B00887.csv',
 'other-Diplo_B01196.csv',
 'other-Federal_02216.csv',
 'other-FHV-services_jan-aug-2015.csv',
 'other-Firstclass_B01536.csv',
 'other-Highclass_B01717.csv',
 'other-Lyft_B02510.csv',
 'other-Prestige_B01338.csv',
 'other-Skyline_B00111.csv',
 'Uber-Jan-Feb-FOIL.csv',
 'uber-raw-data-apr14.csv',
 'uber-raw-data-aug14.csv',
 'uber-raw-data-janjune-15.csv',
 'uber-raw-data-janjune-15_sample.csv',
 'uber-raw-data-jul14.csv',
 'uber-raw-data-jun14.csv',
 'uber-raw-data-may14.csv',
 'uber-raw-data-sep14.csv']

uber = pd.read_csv(r"C:\Users\kisha\OneDrive\Desktop\DOCUMENTS\data analyst\projects\Datasets\uber-raw-data-janjune-15_sample.csv")

uber

	Dispatching_base_num	Pickup_date	Affiliated_base_num	locationID
0	B02617	2015-05-02 21:43:00	B02764	237
1	B02682	2015-01-20 19:52:59	B02682	231
2	B02617	2015-03-19 20:26:00	B02617	161
3	B02764	2015-04-10 17:38:00	B02764	107
4	B02764	2015-03-23 07:03:00	B00111	140
...	...	...	...	...
99995	B02764	2015-04-13 16:12:00	B02764	234
99996	B02764	2015-03-06 21:32:00	B02764	24
99997	B02598	2015-03-19 19:56:00	B02598	17
99998	B02682	2015-05-02 16:02:00	B02682	68
99999	B02764	2015-06-24 16:04:00	B02764	125

100000 rows × 4 columns

uber.shape

(100000, 4)

type(uber)

pandas.core.frame.DataFrame

uber.duplicated().sum()

54

uber.drop_duplicates(inplace=True)

uber.duplicated().sum()

uber.shape

(99946, 4)

uber.dtypes

Dispatching_base_num    object
Pickup_date             object
Affiliated_base_num     object
locationID               int64
dtype: object

uber.isnull().sum()

Dispatching_base_num       0
Pickup_date                0
Affiliated_base_num     1116
locationID                 0
dtype: int64

uber['Pickup_date'][0]

'2015-05-02 21:43:00'

type(uber['Pickup_date'][0])

str

uber['Pickup_date']= pd.to_datetime(uber['Pickup_date'])

type(uber['Pickup_date'][0])

pandas._libs.tslibs.timestamps.Timestamp

uber.dtypes

Dispatching_base_num            object
Pickup_date             datetime64[ns]
Affiliated_base_num             object
locationID                       int64
dtype: object

uber

	Dispatching_base_num	Pickup_date	Affiliated_base_num	locationID
0	B02617	2015-05-02 21:43:00	B02764	237
1	B02682	2015-01-20 19:52:59	B02682	231
2	B02617	2015-03-19 20:26:00	B02617	161
3	B02764	2015-04-10 17:38:00	B02764	107
4	B02764	2015-03-23 07:03:00	B00111	140
...	...	...	...	...
99995	B02764	2015-04-13 16:12:00	B02764	234
99996	B02764	2015-03-06 21:32:00	B02764	24
99997	B02598	2015-03-19 19:56:00	B02598	17
99998	B02682	2015-05-02 16:02:00	B02682	68
99999	B02764	2015-06-24 16:04:00	B02764	125

99946 rows × 4 columns

uber['Month'] = uber['Pickup_date'].dt.month_name()

uber['Month']

0            May
1        January
2          March
3          April
4          March
          ...   
99995      April
99996      March
99997      March
99998        May
99999       June
Name: Month, Length: 99946, dtype: object

uber['Month'].value_counts().plot(kind='bar')

<Axes: >

uber['WeekDay'] = uber['Pickup_date'].dt.day_name()
uber['Day'] = uber['Pickup_date'].dt.day
uber['Hour']  = uber['Pickup_date'].dt.hour
uber['Minute'] = uber['Pickup_date'].dt.minute

uber.head()

	Dispatching_base_num	Pickup_date	Affiliated_base_num	locationID	Month	WeekDay	Day	Hour	Minute
0	B02617	2015-05-02 21:43:00	B02764	237	May	Saturday	2	21	43
1	B02682	2015-01-20 19:52:59	B02682	231	January	Tuesday	20	19	52
2	B02617	2015-03-19 20:26:00	B02617	161	March	Thursday	19	20	26
3	B02764	2015-04-10 17:38:00	B02764	107	April	Friday	10	17	38
4	B02764	2015-03-23 07:03:00	B00111	140	March	Monday	23	7	3

pivot_table = pd.crosstab(index=uber['Month'] , columns =uber['WeekDay'] )

pivot_table

WeekDay	Friday	Monday	Saturday	Sunday	Thursday	Tuesday	Wednesday
Month
April	2365	1833	2508	2052	2823	1880	2521
February	2655	1970	2550	2183	2396	2129	2013
January	2508	1353	2745	1651	2378	1444	1740
June	2793	2848	3037	2485	2767	3187	2503
March	2465	2115	2522	2379	2093	2388	2007
May	3262	1865	3519	2944	2627	2115	2328

pivot_table.plot(kind="bar" , figsize=(8,6),title ="Max Uber Pickups in New York City by month and Days" )

<Axes: title={'center': 'Max Uber Pickups in New York City by month and Days'}, xlabel='Month'>

summary = uber.groupby(['WeekDay' , 'Hour'], as_index=False).size()

summary

	WeekDay	Hour	size
0	Friday	0	581
1	Friday	1	333
2	Friday	2	197
3	Friday	3	138
4	Friday	4	161
...	...	...	...
163	Wednesday	19	1044
164	Wednesday	20	897
165	Wednesday	21	949
166	Wednesday	22	900
167	Wednesday	23	669

168 rows × 3 columns

plt.figure(figsize(8,6))
sns.pointplot(x="Hour",y="size",hue="WeekDay",data=summary)

---------------------------------------------------------------------------
NameError                                 Traceback (most recent call last)
Cell In[151], line 1
----> 1 plt.figure(figsize(8,6))
      2 sns.pointplot(x="Hour",y="size",hue="WeekDay",data=summary)

NameError: name 'figsize' is not defined

uber.columns

Index(['Dispatching_base_num', 'Pickup_date', 'Affiliated_base_num',
       'locationID', 'Month', 'WeekDay', 'Day', 'Hour', 'Minute'],
      dtype='object')

os.listdir(r"C:\Users\kisha\OneDrive\Desktop\DOCUMENTS\data analyst\projects\Datasets")

['other-American_B01362.csv',
 'other-Carmel_B00256.csv',
 'other-Dial7_B00887.csv',
 'other-Diplo_B01196.csv',
 'other-Federal_02216.csv',
 'other-FHV-services_jan-aug-2015.csv',
 'other-Firstclass_B01536.csv',
 'other-Highclass_B01717.csv',
 'other-Lyft_B02510.csv',
 'other-Prestige_B01338.csv',
 'other-Skyline_B00111.csv',
 'Uber-Jan-Feb-FOIL.csv',
 'uber-raw-data-apr14.csv',
 'uber-raw-data-aug14.csv',
 'uber-raw-data-janjune-15.csv',
 'uber-raw-data-janjune-15_sample.csv',
 'uber-raw-data-jul14.csv',
 'uber-raw-data-jun14.csv',
 'uber-raw-data-may14.csv',
 'uber-raw-data-sep14.csv']

uber_foil = pd.read_csv(r"C:\Users\kisha\OneDrive\Desktop\DOCUMENTS\data analyst\projects\Datasets\Uber-Jan-Feb-FOIL.csv")

uber_foil

	dispatching_base_number	date	active_vehicles	trips
0	B02512	1/1/2015	190	1132
1	B02765	1/1/2015	225	1765
2	B02764	1/1/2015	3427	29421
3	B02682	1/1/2015	945	7679
4	B02617	1/1/2015	1228	9537
...	...	...	...	...
349	B02764	2/28/2015	3952	39812
350	B02617	2/28/2015	1372	14022
351	B02682	2/28/2015	1386	14472
352	B02512	2/28/2015	230	1803
353	B02765	2/28/2015	747	7753

354 rows × 4 columns

uber_foil.shape

(354, 4)

!pip install chart_studio
!pip install plotly

Requirement already satisfied: chart_studio in c:\users\kisha\anaconda3\lib\site-packages (1.1.0)
Requirement already satisfied: retrying>=1.3.3 in c:\users\kisha\anaconda3\lib\site-packages (from chart_studio) (1.3.4)
Requirement already satisfied: plotly in c:\users\kisha\anaconda3\lib\site-packages (from chart_studio) (5.9.0)
Requirement already satisfied: requests in c:\users\kisha\anaconda3\lib\site-packages (from chart_studio) (2.28.1)
Requirement already satisfied: six in c:\users\kisha\anaconda3\lib\site-packages (from chart_studio) (1.16.0)
Requirement already satisfied: tenacity>=6.2.0 in c:\users\kisha\anaconda3\lib\site-packages (from plotly->chart_studio) (8.0.1)
Requirement already satisfied: idna<4,>=2.5 in c:\users\kisha\anaconda3\lib\site-packages (from requests->chart_studio) (3.4)
Requirement already satisfied: certifi>=2017.4.17 in c:\users\kisha\anaconda3\lib\site-packages (from requests->chart_studio) (2023.5.7)
Requirement already satisfied: urllib3<1.27,>=1.21.1 in c:\users\kisha\anaconda3\lib\site-packages (from requests->chart_studio) (1.26.14)
Requirement already satisfied: charset-normalizer<3,>=2 in c:\users\kisha\anaconda3\lib\site-packages (from requests->chart_studio) (2.0.4)
Requirement already satisfied: plotly in c:\users\kisha\anaconda3\lib\site-packages (5.9.0)
Requirement already satisfied: tenacity>=6.2.0 in c:\users\kisha\anaconda3\lib\site-packages (from plotly) (8.0.1)

import chart_studio.plotly as py
import plotly.graph_objs as go
import plotly.express as px

from plotly.offline import download_plotlyjs , init_notebook_mode , plot , iplot

uber_foil.columns

Index(['dispatching_base_number', 'date', 'active_vehicles', 'trips'], dtype='object')

px.box(x='dispatching_base_number' , y='active_vehicles' , data_frame=uber_foil)

px.violin(x='dispatching_base_number' , y='active_vehicles' , data_frame=uber_foil)

files = os.listdir(r"C:\Users\kisha\OneDrive\Desktop\DOCUMENTS\data analyst\projects\Datasets")[-8:]

files

['uber-raw-data-apr14.csv',
 'uber-raw-data-aug14.csv',
 'uber-raw-data-janjune-15.csv',
 'uber-raw-data-janjune-15_sample.csv',
 'uber-raw-data-jul14.csv',
 'uber-raw-data-jun14.csv',
 'uber-raw-data-may14.csv',
 'uber-raw-data-sep14.csv']

files.remove('uber-raw-data-janjune-15.csv')

files

['uber-raw-data-apr14.csv',
 'uber-raw-data-aug14.csv',
 'uber-raw-data-janjune-15_sample.csv',
 'uber-raw-data-jul14.csv',
 'uber-raw-data-jun14.csv',
 'uber-raw-data-may14.csv',
 'uber-raw-data-sep14.csv']

files.remove('uber-raw-data-janjune-15_sample.csv')

files

['uber-raw-data-apr14.csv',
 'uber-raw-data-aug14.csv',
 'uber-raw-data-jul14.csv',
 'uber-raw-data-jun14.csv',
 'uber-raw-data-may14.csv',
 'uber-raw-data-sep14.csv']

final = pd.DataFrame()
path =  r"C:\Users\kisha\OneDrive\Desktop\DOCUMENTS\data analyst\projects\Datasets"
for file in files : 
    current_df = pd.read_csv(path+'/'+file)
    final = pd.concat([current_df,final])

final.shape

(4534327, 4)

final.duplicated().sum()

82581

final.drop_duplicates(inplace=True)

final.shape

(4451746, 4)

final.head(3)

	Date/Time	Lat	Lon	Base
0	9/1/2014 0:01:00	40.2201	-74.0021	B02512
1	9/1/2014 0:01:00	40.7500	-74.0027	B02512
2	9/1/2014 0:03:00	40.7559	-73.9864	B02512

rush_uber = final.groupby(['Lat','Lon'],as_index = False).size()

rush_uber

	Lat	Lon	size
0	39.6569	-74.2258	1
1	39.6686	-74.1607	1
2	39.7214	-74.2446	1
3	39.8416	-74.1512	1
4	39.9055	-74.0791	1
...	...	...	...
574553	41.3730	-72.9237	1
574554	41.3737	-73.7988	1
574555	41.5016	-72.8987	1
574556	41.5276	-72.7734	1
574557	42.1166	-72.0666	1

574558 rows × 3 columns

!pip install folium

Requirement already satisfied: folium in c:\users\kisha\anaconda3\lib\site-packages (0.14.0)
Requirement already satisfied: jinja2>=2.9 in c:\users\kisha\anaconda3\lib\site-packages (from folium) (3.1.2)
Requirement already satisfied: numpy in c:\users\kisha\anaconda3\lib\site-packages (from folium) (1.23.5)
Requirement already satisfied: branca>=0.6.0 in c:\users\kisha\anaconda3\lib\site-packages (from folium) (0.6.0)
Requirement already satisfied: requests in c:\users\kisha\anaconda3\lib\site-packages (from folium) (2.28.1)
Requirement already satisfied: MarkupSafe>=2.0 in c:\users\kisha\anaconda3\lib\site-packages (from jinja2>=2.9->folium) (2.1.1)
Requirement already satisfied: certifi>=2017.4.17 in c:\users\kisha\anaconda3\lib\site-packages (from requests->folium) (2023.5.7)
Requirement already satisfied: charset-normalizer<3,>=2 in c:\users\kisha\anaconda3\lib\site-packages (from requests->folium) (2.0.4)
Requirement already satisfied: idna<4,>=2.5 in c:\users\kisha\anaconda3\lib\site-packages (from requests->folium) (3.4)
Requirement already satisfied: urllib3<1.27,>=1.21.1 in c:\users\kisha\anaconda3\lib\site-packages (from requests->folium) (1.26.14)

import folium

basemap = folium.Map()

from folium.plugins import HeatMap

HeatMap(rush_uber).add_to(basemap)

<folium.plugins.heat_map.HeatMap at 0x2bb194b9060>

basemap

Make this Notebook Trusted to load map: File -> Trust Notebook

final.columns

Index(['Date/Time', 'Lat', 'Lon', 'Base'], dtype='object')

final.head()

	Date/Time	Lat	Lon	Base
0	9/1/2014 0:01:00	40.2201	-74.0021	B02512
1	9/1/2014 0:01:00	40.7500	-74.0027	B02512
2	9/1/2014 0:03:00	40.7559	-73.9864	B02512
3	9/1/2014 0:06:00	40.7450	-73.9889	B02512
4	9/1/2014 0:11:00	40.8145	-73.9444	B02512

final.dtypes

Date/Time     object
Lat          float64
Lon          float64
Base          object
dtype: object

final['Date/Time'] = pd.to_datetime(final['Date/Time'],format ="%m/%d/%Y %H:%M:%S")

final['Date/Time'].dtype

dtype('<M8[ns]')

final['Day']=final['Date/Time'].dt.day
final['Hour']=final['Date/Time'].dt.hour

final.head()

	Date/Time	Lat	Lon	Base	Day	Hour
0	2014-09-01 00:01:00	40.2201	-74.0021	B02512	1	0
1	2014-09-01 00:01:00	40.7500	-74.0027	B02512	1	0
2	2014-09-01 00:03:00	40.7559	-73.9864	B02512	1	0
3	2014-09-01 00:06:00	40.7450	-73.9889	B02512	1	0
4	2014-09-01 00:11:00	40.8145	-73.9444	B02512	1	0

pivot = final.groupby(['Day','Hour']).size().unstack()

pivot

Hour	0	1	2	3	4	5	6	7	8	9	...	14	15	16	17	18	19	20	21	22	23
Day
1	3178	1944	1256	1308	1429	2126	3664	5380	5292	4617	...	6933	7910	8633	9511	8604	8001	7315	7803	6268	4050
2	2435	1569	1087	1414	1876	2812	4920	6544	6310	4712	...	6904	8449	10109	11100	11123	9474	8759	8357	6998	5160
3	3354	2142	1407	1467	1550	2387	4241	5663	5386	4657	...	7226	8850	10314	10491	11239	9599	9026	8531	7142	4686
4	2897	1688	1199	1424	1696	2581	4592	6029	5704	4744	...	7158	8515	9492	10357	10259	9097	8358	8649	7706	5130
5	2733	1541	1030	1253	1617	2900	4814	6261	6469	5530	...	6955	8312	9609	10699	10170	9430	9354	9610	8853	6518
6	4537	2864	1864	1555	1551	2162	3642	4766	4942	4401	...	7235	8612	9444	9929	9263	8405	8117	8567	7852	5946
7	3645	2296	1507	1597	1763	2422	4102	5575	5376	4639	...	7276	8474	10393	11013	10573	9472	8691	8525	7194	4801
8	2830	1646	1123	1483	1889	3224	5431	7361	7357	5703	...	7240	8775	9851	10673	9687	8796	8604	8367	6795	4256
9	2657	1724	1222	1480	1871	3168	5802	7592	7519	5895	...	7877	9220	10270	11910	11449	9804	8909	8665	7499	5203
10	3296	2126	1464	1434	1591	2594	4664	6046	6158	5072	...	7612	9578	11045	11875	10934	9613	9687	9240	7766	5496
11	3036	1665	1095	1424	1842	2520	4954	6876	6871	5396	...	7503	8920	10125	10898	10361	9327	8824	8730	7771	5360
12	3227	2147	1393	1362	1757	2710	4576	6250	6231	5177	...	7743	9390	10734	11713	12216	10393	9965	10310	9992	7945
13	5408	3509	2262	1832	1705	2327	4196	5685	6060	5631	...	8200	9264	10534	11826	11450	9921	8705	8423	7363	5936
14	3748	2349	1605	1656	1756	2629	4257	5781	5520	4824	...	6963	8192	9511	10115	9553	9146	9182	8589	6891	4460
15	2497	1515	1087	1381	1862	2980	5050	6837	6729	5201	...	7633	8505	10285	11959	11728	11032	10509	9105	7153	4480
16	2547	1585	1119	1395	1818	2966	5558	7517	7495	5958	...	7597	9290	10804	11773	10855	10924	10142	10374	8094	5380
17	3155	2048	1500	1488	1897	2741	4562	6315	5882	4934	...	7472	8997	10323	11236	11089	9919	9935	9823	8362	5699
18	3390	2135	1332	1626	1892	2959	4688	6618	6451	5377	...	7534	9040	10274	10692	10338	9551	9310	9285	8015	5492
19	3217	2188	1604	1675	1810	2639	4733	6159	6014	5006	...	7374	8898	9893	10741	10429	9701	10051	10049	9090	6666
20	4475	3190	2100	1858	1618	2143	3584	4900	5083	4765	...	7462	8630	9448	10046	9272	8592	8614	8703	7787	5907
21	4294	3194	1972	1727	1926	2615	4185	5727	5529	4707	...	7064	8127	9483	9817	9291	8317	8107	8245	7362	5231
22	2787	1637	1175	1468	1934	3151	5204	6872	6850	5198	...	7337	9148	10574	10962	9884	8980	8772	8430	6784	4530
23	2546	1580	1136	1429	1957	3132	5204	6890	6436	5177	...	7575	9309	9980	10341	10823	11347	11447	10347	8637	5577
24	3200	2055	1438	1493	1798	2754	4484	6013	5913	5146	...	7083	8706	10366	10786	9772	9080	9213	8831	7480	4456
25	2405	1499	1072	1439	1943	2973	5356	7627	7078	5994	...	7298	8732	9922	10504	10673	9048	8751	9508	8522	6605
26	3810	3065	2046	1806	1730	2337	3776	5172	5071	4808	...	7269	8815	9885	10697	10867	10122	9820	10441	9486	7593
27	5196	3635	2352	2055	1723	2336	3539	4937	5053	4771	...	7519	8803	9793	9838	9228	8267	7908	8507	7720	6046
28	4123	2646	1843	1802	1883	2793	4290	5715	5671	5206	...	7341	8584	9671	9975	9132	8255	8309	7949	6411	4461
29	2678	1827	1409	1678	1948	3056	5213	6852	6695	5481	...	7630	9249	10105	11113	10411	9301	9270	9114	6992	4323
30	2401	1510	1112	1403	1841	3216	5757	7596	7611	6064	...	8396	10243	11554	12126	12561	11024	10836	10042	8275	4723
31	2174	1394	1087	919	773	997	1561	2169	2410	2525	...	4104	5099	5386	5308	5350	4898	4819	5064	5164	3961

31 rows × 24 columns

pivot.style.background_gradient()

Hour	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
Day
1	3178	1944	1256	1308	1429	2126	3664	5380	5292	4617	4607	4729	4930	5794	6933	7910	8633	9511	8604	8001	7315	7803	6268	4050
2	2435	1569	1087	1414	1876	2812	4920	6544	6310	4712	4797	4975	5188	5695	6904	8449	10109	11100	11123	9474	8759	8357	6998	5160
3	3354	2142	1407	1467	1550	2387	4241	5663	5386	4657	4788	5065	5384	6093	7226	8850	10314	10491	11239	9599	9026	8531	7142	4686
4	2897	1688	1199	1424	1696	2581	4592	6029	5704	4744	4743	4975	5193	6175	7158	8515	9492	10357	10259	9097	8358	8649	7706	5130
5	2733	1541	1030	1253	1617	2900	4814	6261	6469	5530	5141	5011	5047	5690	6955	8312	9609	10699	10170	9430	9354	9610	8853	6518
6	4537	2864	1864	1555	1551	2162	3642	4766	4942	4401	4801	5174	5426	6258	7235	8612	9444	9929	9263	8405	8117	8567	7852	5946
7	3645	2296	1507	1597	1763	2422	4102	5575	5376	4639	4905	5166	5364	6214	7276	8474	10393	11013	10573	9472	8691	8525	7194	4801
8	2830	1646	1123	1483	1889	3224	5431	7361	7357	5703	5288	5350	5483	6318	7240	8775	9851	10673	9687	8796	8604	8367	6795	4256
9	2657	1724	1222	1480	1871	3168	5802	7592	7519	5895	5406	5443	5496	6419	7877	9220	10270	11910	11449	9804	8909	8665	7499	5203
10	3296	2126	1464	1434	1591	2594	4664	6046	6158	5072	4976	5415	5506	6527	7612	9578	11045	11875	10934	9613	9687	9240	7766	5496
11	3036	1665	1095	1424	1842	2520	4954	6876	6871	5396	5215	5423	5513	6486	7503	8920	10125	10898	10361	9327	8824	8730	7771	5360
12	3227	2147	1393	1362	1757	2710	4576	6250	6231	5177	5157	5319	5570	6448	7743	9390	10734	11713	12216	10393	9965	10310	9992	7945
13	5408	3509	2262	1832	1705	2327	4196	5685	6060	5631	5442	5720	5914	6678	8200	9264	10534	11826	11450	9921	8705	8423	7363	5936
14	3748	2349	1605	1656	1756	2629	4257	5781	5520	4824	4911	5118	5153	5747	6963	8192	9511	10115	9553	9146	9182	8589	6891	4460
15	2497	1515	1087	1381	1862	2980	5050	6837	6729	5201	5347	5517	5503	6997	7633	8505	10285	11959	11728	11032	10509	9105	7153	4480
16	2547	1585	1119	1395	1818	2966	5558	7517	7495	5958	5626	5480	5525	6198	7597	9290	10804	11773	10855	10924	10142	10374	8094	5380
17	3155	2048	1500	1488	1897	2741	4562	6315	5882	4934	5004	5306	5634	6507	7472	8997	10323	11236	11089	9919	9935	9823	8362	5699
18	3390	2135	1332	1626	1892	2959	4688	6618	6451	5377	5150	5487	5490	6383	7534	9040	10274	10692	10338	9551	9310	9285	8015	5492
19	3217	2188	1604	1675	1810	2639	4733	6159	6014	5006	5092	5240	5590	6367	7374	8898	9893	10741	10429	9701	10051	10049	9090	6666
20	4475	3190	2100	1858	1618	2143	3584	4900	5083	4765	5135	5650	5745	6656	7462	8630	9448	10046	9272	8592	8614	8703	7787	5907
21	4294	3194	1972	1727	1926	2615	4185	5727	5529	4707	4911	5212	5465	6085	7064	8127	9483	9817	9291	8317	8107	8245	7362	5231
22	2787	1637	1175	1468	1934	3151	5204	6872	6850	5198	5277	5352	5512	6342	7337	9148	10574	10962	9884	8980	8772	8430	6784	4530
23	2546	1580	1136	1429	1957	3132	5204	6890	6436	5177	5066	5304	5504	6232	7575	9309	9980	10341	10823	11347	11447	10347	8637	5577
24	3200	2055	1438	1493	1798	2754	4484	6013	5913	5146	4947	5311	5229	5974	7083	8706	10366	10786	9772	9080	9213	8831	7480	4456
25	2405	1499	1072	1439	1943	2973	5356	7627	7078	5994	5432	5504	5694	6204	7298	8732	9922	10504	10673	9048	8751	9508	8522	6605
26	3810	3065	2046	1806	1730	2337	3776	5172	5071	4808	5061	5179	5381	6166	7269	8815	9885	10697	10867	10122	9820	10441	9486	7593
27	5196	3635	2352	2055	1723	2336	3539	4937	5053	4771	5198	5732	5839	6820	7519	8803	9793	9838	9228	8267	7908	8507	7720	6046
28	4123	2646	1843	1802	1883	2793	4290	5715	5671	5206	5247	5500	5486	6120	7341	8584	9671	9975	9132	8255	8309	7949	6411	4461
29	2678	1827	1409	1678	1948	3056	5213	6852	6695	5481	5234	5163	5220	6305	7630	9249	10105	11113	10411	9301	9270	9114	6992	4323
30	2401	1510	1112	1403	1841	3216	5757	7596	7611	6064	5987	6090	6423	7249	8396	10243	11554	12126	12561	11024	10836	10042	8275	4723
31	2174	1394	1087	919	773	997	1561	2169	2410	2525	2564	2777	2954	3280	4104	5099	5386	5308	5350	4898	4819	5064	5164	3961

def gen_pivot_table(df,col1,col2):
    pivot = final.groupby(['Day','Hour']).size().unstack()
    return pivot.style.background_gradient()

final.columns

Index(['Date/Time', 'Lat', 'Lon', 'Base', 'Day', 'Hour'], dtype='object')

gen_pivot_table(final , "Day" , "Hour")

Hour	0	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23
Day
1	3178	1944	1256	1308	1429	2126	3664	5380	5292	4617	4607	4729	4930	5794	6933	7910	8633	9511	8604	8001	7315	7803	6268	4050
2	2435	1569	1087	1414	1876	2812	4920	6544	6310	4712	4797	4975	5188	5695	6904	8449	10109	11100	11123	9474	8759	8357	6998	5160
3	3354	2142	1407	1467	1550	2387	4241	5663	5386	4657	4788	5065	5384	6093	7226	8850	10314	10491	11239	9599	9026	8531	7142	4686
4	2897	1688	1199	1424	1696	2581	4592	6029	5704	4744	4743	4975	5193	6175	7158	8515	9492	10357	10259	9097	8358	8649	7706	5130
5	2733	1541	1030	1253	1617	2900	4814	6261	6469	5530	5141	5011	5047	5690	6955	8312	9609	10699	10170	9430	9354	9610	8853	6518
6	4537	2864	1864	1555	1551	2162	3642	4766	4942	4401	4801	5174	5426	6258	7235	8612	9444	9929	9263	8405	8117	8567	7852	5946
7	3645	2296	1507	1597	1763	2422	4102	5575	5376	4639	4905	5166	5364	6214	7276	8474	10393	11013	10573	9472	8691	8525	7194	4801
8	2830	1646	1123	1483	1889	3224	5431	7361	7357	5703	5288	5350	5483	6318	7240	8775	9851	10673	9687	8796	8604	8367	6795	4256
9	2657	1724	1222	1480	1871	3168	5802	7592	7519	5895	5406	5443	5496	6419	7877	9220	10270	11910	11449	9804	8909	8665	7499	5203
10	3296	2126	1464	1434	1591	2594	4664	6046	6158	5072	4976	5415	5506	6527	7612	9578	11045	11875	10934	9613	9687	9240	7766	5496
11	3036	1665	1095	1424	1842	2520	4954	6876	6871	5396	5215	5423	5513	6486	7503	8920	10125	10898	10361	9327	8824	8730	7771	5360
12	3227	2147	1393	1362	1757	2710	4576	6250	6231	5177	5157	5319	5570	6448	7743	9390	10734	11713	12216	10393	9965	10310	9992	7945
13	5408	3509	2262	1832	1705	2327	4196	5685	6060	5631	5442	5720	5914	6678	8200	9264	10534	11826	11450	9921	8705	8423	7363	5936
14	3748	2349	1605	1656	1756	2629	4257	5781	5520	4824	4911	5118	5153	5747	6963	8192	9511	10115	9553	9146	9182	8589	6891	4460
15	2497	1515	1087	1381	1862	2980	5050	6837	6729	5201	5347	5517	5503	6997	7633	8505	10285	11959	11728	11032	10509	9105	7153	4480
16	2547	1585	1119	1395	1818	2966	5558	7517	7495	5958	5626	5480	5525	6198	7597	9290	10804	11773	10855	10924	10142	10374	8094	5380
17	3155	2048	1500	1488	1897	2741	4562	6315	5882	4934	5004	5306	5634	6507	7472	8997	10323	11236	11089	9919	9935	9823	8362	5699
18	3390	2135	1332	1626	1892	2959	4688	6618	6451	5377	5150	5487	5490	6383	7534	9040	10274	10692	10338	9551	9310	9285	8015	5492
19	3217	2188	1604	1675	1810	2639	4733	6159	6014	5006	5092	5240	5590	6367	7374	8898	9893	10741	10429	9701	10051	10049	9090	6666
20	4475	3190	2100	1858	1618	2143	3584	4900	5083	4765	5135	5650	5745	6656	7462	8630	9448	10046	9272	8592	8614	8703	7787	5907
21	4294	3194	1972	1727	1926	2615	4185	5727	5529	4707	4911	5212	5465	6085	7064	8127	9483	9817	9291	8317	8107	8245	7362	5231
22	2787	1637	1175	1468	1934	3151	5204	6872	6850	5198	5277	5352	5512	6342	7337	9148	10574	10962	9884	8980	8772	8430	6784	4530
23	2546	1580	1136	1429	1957	3132	5204	6890	6436	5177	5066	5304	5504	6232	7575	9309	9980	10341	10823	11347	11447	10347	8637	5577
24	3200	2055	1438	1493	1798	2754	4484	6013	5913	5146	4947	5311	5229	5974	7083	8706	10366	10786	9772	9080	9213	8831	7480	4456
25	2405	1499	1072	1439	1943	2973	5356	7627	7078	5994	5432	5504	5694	6204	7298	8732	9922	10504	10673	9048	8751	9508	8522	6605
26	3810	3065	2046	1806	1730	2337	3776	5172	5071	4808	5061	5179	5381	6166	7269	8815	9885	10697	10867	10122	9820	10441	9486	7593
27	5196	3635	2352	2055	1723	2336	3539	4937	5053	4771	5198	5732	5839	6820	7519	8803	9793	9838	9228	8267	7908	8507	7720	6046
28	4123	2646	1843	1802	1883	2793	4290	5715	5671	5206	5247	5500	5486	6120	7341	8584	9671	9975	9132	8255	8309	7949	6411	4461
29	2678	1827	1409	1678	1948	3056	5213	6852	6695	5481	5234	5163	5220	6305	7630	9249	10105	11113	10411	9301	9270	9114	6992	4323
30	2401	1510	1112	1403	1841	3216	5757	7596	7611	6064	5987	6090	6423	7249	8396	10243	11554	12126	12561	11024	10836	10042	8275	4723
31	2174	1394	1087	919	773	997	1561	2169	2410	2525	2564	2777	2954	3280	4104	5099	5386	5308	5350	4898	4819	5064	5164	3961